#!/bin/sh
echo "开始创建ods层数据库的表结构！"
db1="ods02"
db2="dw02"
db3="dm02"

echo "开始创建数据库====================="

echo "创建数据库${db1}"
hive -e "create database if not exists ${db1};"
echo $?
echo "完成创建数据库${db1}"

echo "创建数据库${db2}"
hive -e "create database if not exists ${db2};"
echo $?
echo "完成创建数据库${db2}"

echo "创建数据库${db3}"
hive -e "create database if not exists ${db3};"
echo $?
echo "完成创建数据库${db3}"

echo "完成创建数据库========================"

---------------------------------------------------------------------------
#!/bin/sh
db1="ods02"
t1="ods_cust_info_02"  #客户基本信息表
echo "创建ods层的落地表${t1}"
c_t="create table if not exists ${db1}.${t1}(
cust_id string,
cust_name string,
product_code int,
txt_amt int,
addrno int,
age int,
dt string)
row format delimited fields terminated by '\t';"
#hive指令创建
echo "$c_t"|hive
echo $?
echo "ods层客户基本表已建立完成"
echo "------------------------------------------------------------------------"
-------------------------------------------------------------------------------
#!/bin/sh
db1="ods02"
db2="dw02"
db3="dm02"
t1="ods_cust_info_02"  #客户基本信息表
t2="dw_cust_info_02" #dw层客户基表
t3="dw_product_info_02" #产品码表
t4="dw_addr_info_02" #地址码表 
t6="dw_product_info_03" # 清洗后产品码表
t7="dw_addr_info_03" #清洗后的地址码表
echo "创建dw层客户基本表${t2}"
c_t2="create table if not exists ${db2}.${t2}(
cust_id string,
cust_name string,
product_code int,
txt_amt int,
addrno int,
age int,
dt string,
etl_dt string
)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$c_t2"
echo $?
echo "dw层客户基表已创建完成"

echo "创建dw层产品码表${t3}"
c_t3="create table if not exists ${db2}.${t3}(
    product_code int,
    product_name string,
    product_type string
)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$c_t3"
echo $?
echo "dw层产品码表已创建完成"

echo "创建dw层地址码表${t4}"
c_t4="create table if not exists ${db2}.${t4}(
    addrno int,
    addrname string,
    addrcode string
)
row format delimited fields terminated by ',';"
#使用hive -e 指令进行创建
hive -e "$c_t4"
echo $?
echo "dw层地址码表已创建完成"

echo "清洗后产品码表${t6}"
c_t6="create table if not exists ${db2}.${t6}(
product_code int,
product_name string,
product_type string
)
row format delimited fields terminated by '\t';"
#使用hive -e 指令进行创建
hive -e "$c_t6"
echo $?
echo "清洗后产品码表${t6}已创建完成"

echo "清洗地址码表${t7}"
c_t7="create table if not exists ${db2}.${t7}(
addrno int,
addrname string,
addrcode string
)
row format delimited fields terminated by ',';"
#使用hive -e 指令进行创建
hive -e "$c_t7"
echo $?
echo "清洗后地址码表${t7}已创建完成"
echo "dw层所有表格已创建完成================="

#!/bin/sh
db3="dm02"
t5="dm_rk_02" #指标落地表
echo "创建dm层指标落地表"
c_t5="create table if not exists ${db3}.${t5}(
addrname string,
product_name string,
s_amt int,
r_n int,
elt_dt string)
row format delimited fields terminated by '\t';"
echo "c_t5"|hive
echo $?
echo"表格已经全部创建完成==============="

#!/bin/sh 5.sh
echo "导入ods的数据到客户基表中"
load_data1="load data local inpath '/home/xiangmu/1.txt' overwrite into table ods02.ods_cust_info_02;"
echo "$load_data1"|hive
echo $?
echo "导入dw的数据到地址码表中"
load_data2="load data local inpath '/home/xiangmu/3.txt' overwrite into table dw02.dw_addr_info_02;"
echo "$load_data2"|hive
echo $?
echo "导入dw的数据到产品码表中"
load_data3="load data local inpath '/home/xiangmu/2.txt' overwrite into table dw02.dw_product_info_02;"
echo "$load_data3"|hive
echo $?
echo "统计三张来源表的数据量"
c=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods02.ods_cust_info_02;"`
echo "客户基表本次抽取的数据量为：${c}条"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw02.dw_addr_info_02;"`
echo "地址码表本次抽取的数据量为：${c1}条"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw02.dw_product_info_02;"`
echo "产品码表本次抽取的数据量为：${c2}条"
echo "抽数完成，请自行验证！"
-------------------------------------------------------------------------------
6.sh
#!/bin/sh
insert_dw_cust="insert overwrite table dw02.dw_cust_info_02
select regexp_replace(cust_id,'[^0-9]+','') cust_id,
regexp_replace(cust_name,'[^一-龥]+','') cust_name,
product_code,
txt_amt,
addrno,
age,
dt,
current_timestamp etl_dt
from ods02.ods_cust_info_02;"
echo "$insert_dw_cust"|hive
echo $?
echo "---清洗完成----"
c=`hive -e "select count(*) from ods02.ods_cust_info_02;"`
echo "客户基表ods的数据量为：${c}条"
c1=`hive -e "select count(*) from dw02.dw_cust_info_02;"`
echo "dw层的数据量为：${c1}条"
if [ "$c1" -eq "$c" ];then
    echo "清洗成功"
else
    echo "清洗失败"
fi

insert_dw_addr="insert overwrite table dw02.dw_addr_info_03
select addrno,addrname,addrcode
from dw02.dw_addr_info_02
group by addrno,addrname,addrcode;"
echo "$insert_dw_addr"|hive
echo $?
echo "清洗完成"

insert_dw_product="insert overwrite table dw02.dw_product_info_03
select product_code, 
product_name, 
product_type
from dw02.dw_product_info_02
group by product_code, 
product_name, 
product_type ;"
echo "$insert_dw_product"|hive
echo $?
echo "清洗完成"

7.sh
#!/bin/sh
dm_rk="insert overwrite table dm02.dm_rk_02
select d.*,
row_number() over(partition by addrname order by s_amt desc) r_n,
current_timestamp etl_dt
from
(select 
nvl(b.addrname,'其他地区') addrname,
nvl(c.product_name ,'其他产品') product_name,
sum(a.txt_amt) s_amt
from dw02.dw_cust_info_02 a left join 
dw_addr_info_03 b on a.addrno=b.addrno
left join dw_product_info_03 c on a.product_code=c.product_code
group by 
nvl(b.addrname,'其他地区'),
nvl(c.product_name,'其他产品')
) d;"
echo "$dm_rk"|hive
echo $?
echo "数据计算已完成"